unit clsItemTabela;

interface
uses Dm, dbtables, biblio, Sysutils;

type
  TVetor_PM = array of integer;

type
  TItemTabela = class(TObject)
  private
    Tab_codigo: string;
    Pro_codigo: string;
    Itt_coluna: string;
  public
    Itt_Preco: double;
    Itt_status: string;
    Itt_Pm: integer;
    function Gravar(p_Tab_codigo, p_Pro_Codigo, P_Itt_coluna: string): Byte;
    function Recuperar(p_Tab_codigo, p_Pro_Codigo, P_Itt_coluna: string): Byte;
    function Excluir(p_Tab_codigo, p_Pro_Codigo, P_Itt_coluna: string): Byte;
    function ExcluirPorTabela(p_Tab_codigo: string): Byte;
    function AlteraStatus(p_Tab_codigo, P_Itt_coluna, P_status: string): byte;
    function RetornaStatus(p_Tab_codigo, P_Itt_coluna: string): string;
    procedure Retornar_PM(p_Tab_Codigo: string; var vetor: TVetor_PM);
    procedure Retornar_PM1(p_Tab_Codigo: string; var p_q: TQuery);
    procedure Consultar(var p_q: TQuery; p_Tab_codigo, p_pro_codigo, P_Itt_coluna: string);
    procedure ConsultarPorTabela(var p_q: Tquery; P_Tab_codigo: string);
    procedure ConsultarPorTabProduto(var p_q: Tquery; P_fase_codigo, P_tab_codigo: string);
    function RecuperaNumColunaAtiva(p_tab_codigo: string): integer;
    function RecuperaPM(p_tab_codigo: string; p_coluna: string): boolean;
    procedure ConsultarProduto(var p_q: Tquery);
  end;
implementation
{ TItemTabela }

function TItemTabela.AlteraStatus(p_Tab_codigo, P_Itt_coluna,
  P_status: string): byte;
var
  tq: TQuery;
begin
  tq := Tquery.Create(dmint);
  tq.databasename := DmInt.db.databasename;
  tq.active := False;
  tq.sql.Clear;
  tq.params.clear;
  tq.sql.add('UPDATE ItemTabela SET ' +
    'Itt_Status   =:K_Itt_status ' +
    'WHERE Tab_codigo=:k_Tab_codigo and Itt_coluna =:K_Itt_coluna');
  tq.parambyname('k_Tab_Codigo').asstring := strzero(p_Tab_codigo, 3);
  tq.parambyname('k_Itt_coluna').asstring := P_Itt_coluna;
  tq.parambyname('k_Itt_Status').asstring := P_status;
  tq.execsql;
  Result := 1;
  tq.close;
  tq.Free;
end;

procedure TItemTabela.Consultar(var p_q: TQuery; p_Tab_codigo,
  p_pro_codigo, P_Itt_coluna: string);
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add('Select * from ItemTabela where Tab_codigo=:k_Tab_codigo and pro_codigo=:k_Pro_codigo and Itt_coluna=:K_Itt_coluna order by pro_codigo');
  p_q.parambyname('k_Tab_codigo').asstring := strzero(p_Tab_codigo, 3);
  p_q.parambyname('k_pro_codigo').asstring := p_pro_codigo;
  p_q.parambyname('K_Itt_coluna').AsString := P_Itt_coluna;
end;

procedure TItemTabela.ConsultarPorTabela(var p_q: Tquery;
  P_Tab_codigo: string);
begin
  p_q.SQL.Clear;
  p_q.Params.Clear;
  p_q.SQL.add('Select * from itemTabela, produto where tab_codigo=:K_P_Tab_Codigo ' +
    'and ItemTabela.pro_codigo = Produto.pro_codigo order by itemtabela.pro_codigo, itemtabela.itt_coluna');
  p_q.ParamByName('K_P_Tab_Codigo').AsString := StrZero(P_Tab_codigo, 3);
end;

procedure TItemTabela.ConsultarPorTabProduto(var p_q: Tquery;
  P_fase_codigo, P_tab_codigo: string);
begin
  p_q.SQL.Clear;
  p_q.Params.Clear;
  P_q.sql.add('Select produto.pro_codigo, produto.pro_nome from  produto ' +
    ' where Produto.fase_codigo =:K_fase_codigo  and produto.pro_codigo not in (' +
    ' select pro_codigo from itemTabela where tab_codigo=:K_Tab_codigo) ');
  p_q.ParamByName('K_Fase_codigo').AsString := StrZero(P_fase_codigo, 2);
  p_q.ParamByName('K_tab_codigo').AsString := StrZero(P_tab_codigo, 3);
end;

procedure TItemTabela.ConsultarProduto(var p_q: Tquery);
begin
  p_q.SQL.Clear;
  p_q.Params.Clear;
  P_q.sql.add('Select pro_codigo, pro_nome from  produto');
end;

function TItemTabela.Excluir(p_Tab_codigo, p_Pro_Codigo, P_itt_coluna: string): Byte;
var
  tq: TQuery;
begin
  tq := Tquery.Create(dmint);
  tq.databasename := DmInt.db.databasename;
  tq.active := False;
  tq.sql.Clear;
  tq.params.clear;
  tq.sql.add('DELETE FROM ItemTabela WHERE Tab_codigo=:k_tab_Codigo and pro_codigo=:K_Pro_codigo and Itt_coluna=:K_Itt_coluna');
  tq.parambyname('k_Tab_Codigo').asstring := strzero(p_Tab_Codigo, 3);
  tq.parambyname('k_Pro_Codigo').asstring := p_pro_Codigo;
  tq.parambyname('k_Itt_coluna').asstring := P_Itt_coluna;
  tq.execSQL;
  Result := 1;
  tq.close;
  tq.Free;
end;

function TItemTabela.ExcluirPorTabela(p_Tab_codigo: string): Byte;
var
  tq: TQuery;
begin
  tq := Tquery.Create(dmint);
  tq.databasename := DmInt.db.databasename;
  tq.active := False;
  tq.sql.Clear;
  tq.params.clear;
  tq.sql.add('DELETE FROM ItemTabela WHERE Tab_codigo=:k_tab_Codigo');
  tq.parambyname('k_Tab_Codigo').asstring := strzero(p_Tab_Codigo, 3);
  tq.execSQL;
  Result := 1;
  tq.close;
  tq.Free;
end;

function TItemTabela.Gravar(p_Tab_codigo, p_Pro_Codigo, P_Itt_coluna: string): Byte;
var
  tq: TQuery;
begin
  tq := Tquery.Create(DmInt);
  tq.DatabaseName := DmInt.Db.DatabaseName;
  tq.active := False;
  tq.sql.clear;
  tq.params.clear;
  tq.sql.add('SELECT * FROM ItemTabela where Tab_Codigo=:k_Tab_Codigo and Pro_codigo=:k_Pro_codigo and Itt_coluna=:K_Itt_coluna');
  tq.parambyname('k_Tab_codigo').asstring := strzero(p_Tab_codigo, 3);
  tq.parambyname('k_pro_codigo').asstring := p_Pro_codigo;
  tq.parambyname('k_Itt_coluna').asstring := P_Itt_coluna;
  tq.open;
  if tq.eof then begin
    tq.sql.clear;
    tq.params.clear;
    tq.sql.add('INSERT INTO ItemTabela VALUES(:k_Tab_codigo,' +
      ':k_pro_codigo,' +
      ':K_Itt_coluna,' +
      ':k_Itt_preco,' +
      ':K_Itt_status,' +
      ':K_Itt_Pm)');
  end
  else begin
    tq.sql.clear;
    tq.params.clear;
    tq.sql.add('UPDATE ItemTabela SET ' +
      'Itt_preco    =:k_Itt_preco, ' +
      'Itt_Status   =:K_Itt_status,' +
      'Itt_Pm       =:K_Itt_Pm     ' +
      'WHERE Tab_codigo=:k_Tab_codigo and Pro_codigo=:k_Pro_codigo and Itt_coluna =:K_Itt_coluna');
  end;
  tq.parambyname('k_Tab_codigo').AsString := StrZero(p_TAb_codigo, 3);
  tq.parambyname('k_Pro_codigo').AsString := p_Pro_codigo;
  tq.parambyname('k_Itt_Preco').AsFloat := Itt_Preco;
  tq.parambyname('k_Itt_Coluna').AsString := P_Itt_coluna;
  tq.parambyname('K_Itt_status').AsString := Itt_status;
  tq.parambyname('K_Itt_Pm').Asinteger := Itt_Pm;
  tq.ExecSQL;
  Result := 1;
  tq.close;
  tq.Free;
end;

function TItemTabela.RecuperaPM(p_tab_codigo: string;
  p_coluna: string): boolean;
var
  tq: TQuery;
begin
  tq := Tquery.Create(dmint);
  tq.databasename := DmInt.db.databasename;
  tq.active := False;
  tq.close;
  tq.sql.Clear;
  tq.params.clear;
  tq.sql.add('Select itt_pm from ItemTabela where tab_codigo=:a and itt_coluna=:b');
  tq.parambyname('a').asstring := p_tab_codigo;
  tq.parambyname('b').asstring := p_coluna;
  tq.open;
  if not tq.eof then begin
    result := true;
    Itt_Pm := tq.fieldbyname('itt_pm').asinteger;
  end else
    result := false;
  tq.close;
  tq.free;
end;

function TItemTabela.RecuperaNumColunaAtiva(p_tab_codigo: string): integer;
var
  tq: TQuery;
  ax: string;
begin
  Result := 0;
  tq := Tquery.Create(dmint);
  tq.databasename := DmInt.db.databasename;
  tq.active := False;
  tq.sql.Clear;
  tq.params.clear;
  tq.sql.add('Select * from ItemTabela WHERE Tab_codigo=:k_Tab_codigo and itt_status is null');
  tq.parambyname('k_Tab_Codigo').asstring := strzero(p_Tab_codigo, 3);
  tq.open;
  if not tq.eof then begin
    ax := tq.fieldbyname('pro_codigo').AsString;
    tq.active := False;
    tq.sql.Clear;
    tq.params.clear;
    tq.sql.add('Select * from ItemTabela WHERE Tab_codigo=:k_Tab_codigo ' +
      'and pro_codigo=:K_pro_codigo and itt_status is null');
    tq.parambyname('k_Tab_Codigo').asstring := strzero(p_Tab_codigo, 3);
    tq.parambyname('k_pro_Codigo').asstring := ax;
    tq.open;
    Result := tq.RecordCount;
  end;
  tq.close;
  tq.Free;
end;

function TItemTabela.Recuperar(p_Tab_codigo, p_Pro_Codigo, P_Itt_coluna: string): Byte;
var
  tq: TQuery;
begin
  tq := Tquery.Create(DmInt.Db);
  tq.databasename := DmInt.Db.DatabaseName;
  tq.active := False;
  tq.sql.clear;
  tq.sql.add('SELECT * FROM ItemTabela where Tab_codigo=:k_TAb_codigo and Pro_codigo=:k_Pro_codigo and Itt_coluna=:K_Itt_coluna');
  tq.Params[0].asstring := strzero(p_Tab_codigo, 3);
  tq.params[1].asstring := p_Pro_codigo;
  tq.params[2].asstring := P_Itt_coluna;
  tq.open;
  Tab_codigo := p_Tab_codigo;
  Pro_codigo := p_Pro_codigo;
  Itt_coluna := P_Itt_coluna;
  if tq.eof then begin
    Tab_codigo := '';
    Pro_codigo := '';
    Itt_coluna := '';
    Itt_preco := 0;
    Itt_Status := '';
    Itt_Pm := 0;
    Result := 0;
  end else begin
    Itt_preco := tq.fieldbyname('itt_preco').asfloat;
    Itt_status := tq.fieldbyname('Itt_Status').AsString;
    Itt_Pm := tq.fieldbyname('Itt_Pm').asinteger;
    Result := 1;
  end;
  tq.Close;
  tq.Free;
end;

procedure TItemTabela.Retornar_PM;
var
  tq: TQuery;
begin
  tq := Tquery.Create(dmint);
  tq.databasename := DmInt.db.databasename;
  tq.active := false;
  tq.sql.Clear;
  tq.params.clear;
  tq.sql.add('Select distinct Itt_Pm from ItemTabela where Tab_codigo=:a order by Itt_Pm');
  tq.params[0].asstring := strzero(p_Tab_Codigo, 3);
  tq.open;
  while not tq.eof do begin
    setlength(vetor, length(vetor) + 1);
    vetor[length(vetor) - 1] := tq.fieldbyname('Itt_Pm').asinteger;
    tq.next;
  end;
  tq.close;
  tq.Free;
end;

procedure TItemTabela.Retornar_PM1(p_Tab_Codigo: string; var p_q: TQuery);
begin
  p_q.close;
  p_q.sql.Clear;
  p_q.params.clear;
  p_q.sql.add('Select distinct Itt_Pm, itt_coluna from ItemTabela where Tab_codigo=:a order by Itt_Pm');
  p_q.params[0].asstring := strzero(p_Tab_Codigo, 3);
end;

function TItemTabela.RetornaStatus(p_Tab_codigo,
  P_Itt_coluna: string): string;
var
  tq: TQuery;
begin
  tq := Tquery.Create(dmint);
  tq.databasename := DmInt.db.databasename;
  tq.active := false;
  tq.sql.Clear;
  tq.params.clear;
  tq.sql.add('Select itt_status from itemtabela where tab_codigo =:a and itt_coluna =:b');
  tq.params[0].asstring := strzero(p_Tab_Codigo, 3);
  tq.Params[1].AsString := P_Itt_coluna;
  tq.open;
  Result := tq.FieldByName('Itt_status').AsString;
  tq.Close;
  tq.Free;
end;

end.
